package com.F06.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F06.actionForm.F060103ActionForm;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.util.CommonTools;

/**
 * @wangk071226
 * @综合统计下载
 * @功能：生成数据倒入EXCEL
 */
public class F060103DAO extends DbOperationExt {

	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F060103DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F060103DAO() {
		super();
	}

	public F060103DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @第一页数据
	 */
	public List sheet0(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		CallableStatement proc = null;
		msql.append("select saleman,area_group,bargainmoney,month1,month2,month3,");
		msql.append("month4,month5,month6,month7,month8,month9,month10,month11,month12");
		msql.append(" from temp_integration_stat ");
		try {
			proc = super.connection.prepareCall("{call PRO_INTEGRATION_STAT()}");
			proc.execute();
			super.connection.commit();
			log.info("综合统计 sheet0 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("销售员", CommonTools.null2String(rest.getString("saleman")));
				map.put("地区", CommonTools.null2String(rest.getString("area_group")));
				map.put("未入库合同金额", CommonTools.null2String(rest.getString("bargainmoney")));
				map.put("month1", CommonTools.null2String(rest.getString("month1")));
				map.put("month2", CommonTools.null2String(rest.getString("month2")));
				map.put("month3", CommonTools.null2String(rest.getString("month3")));
				map.put("month4", CommonTools.null2String(rest.getString("month4")));
				map.put("month5", CommonTools.null2String(rest.getString("month5")));
				map.put("month6", CommonTools.null2String(rest.getString("month6")));
				map.put("month7", CommonTools.null2String(rest.getString("month7")));
				map.put("month8", CommonTools.null2String(rest.getString("month8")));
				map.put("month9", CommonTools.null2String(rest.getString("month9")));
				map.put("month10", CommonTools.null2String(rest.getString("month10")));
				map.put("month11", CommonTools.null2String(rest.getString("month11")));
				map.put("month12", CommonTools.null2String(rest.getString("month12")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}

	/**
	 * @第二页数据
	 */
	public List sheet1(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		CallableStatement proc = null;
		msql.append(" SELECT product_type,quantity_lastyear,total_price_lastyear, ");
		msql.append("   total_price1,quantity1,total_price2,quantity2, ");
		msql.append("   total_price3,quantity3,total_price4,quantity4,total_price5, ");
		msql.append("   quantity5,total_price6,quantity6,total_price7,quantity7, ");
		msql.append("   total_price8,quantity8,total_price9,quantity9,total_price10, ");
		msql.append("   quantity10,total_price11,quantity11,total_price12,quantity12 ");
		msql.append(" FROM ");
		msql.append("  temp_sort_stat ");
		try {
			proc = super.connection.prepareCall("{call PRO_SORT_STAT()}");
			proc.execute();
			super.connection.commit();
			log.info("综合统计 sheet1 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("product_type", CommonTools.null2String(rest.getString("product_type")));
				map.put("quantity_lastyear", CommonTools.null2String(rest.getString("quantity_lastyear")));
				map.put("total_price_lastyear", CommonTools.null2String(rest.getString("total_price_lastyear")));
				map.put("total_price1", CommonTools.null2String(rest.getString("total_price1")));
				map.put("quantity1", CommonTools.null2String(rest.getString("quantity1")));
				map.put("total_price2", CommonTools.null2String(rest.getString("total_price2")));
				map.put("quantity2", CommonTools.null2String(rest.getString("quantity2")));
				map.put("total_price3", CommonTools.null2String(rest.getString("total_price3")));
				map.put("quantity3", CommonTools.null2String(rest.getString("quantity3")));
				map.put("total_price4", CommonTools.null2String(rest.getString("total_price4")));
				map.put("quantity4", CommonTools.null2String(rest.getString("quantity4")));
				map.put("total_price5", CommonTools.null2String(rest.getString("total_price5")));
				map.put("quantity5", CommonTools.null2String(rest.getString("quantity5")));
				map.put("total_price6", CommonTools.null2String(rest.getString("total_price6")));
				map.put("quantity6", CommonTools.null2String(rest.getString("quantity6")));
				map.put("total_price7", CommonTools.null2String(rest.getString("total_price7")));
				map.put("quantity7", CommonTools.null2String(rest.getString("quantity7")));
				map.put("total_price8", CommonTools.null2String(rest.getString("total_price8")));
				map.put("quantity8", CommonTools.null2String(rest.getString("quantity8")));
				map.put("total_price9", CommonTools.null2String(rest.getString("total_price9")));
				map.put("quantity9", CommonTools.null2String(rest.getString("quantity9")));
				map.put("total_price10", CommonTools.null2String(rest.getString("total_price10")));
				map.put("quantity10", CommonTools.null2String(rest.getString("quantity10")));
				map.put("total_price11", CommonTools.null2String(rest.getString("total_price11")));
				map.put("quantity11", CommonTools.null2String(rest.getString("quantity11")));
				map.put("total_price12", CommonTools.null2String(rest.getString("total_price12")));
				map.put("quantity12", CommonTools.null2String(rest.getString("quantity12")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}

	/**
	 * @第三页数据
	 */
	public List sheet2(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		CallableStatement proc = null;
		msql.append(" SELECT ");
		msql.append("   saleman,area_group,month1,month2,month3,month4,month5,");
		msql.append("   month6,month7,month8,month9,month10,month11,month12 ");
		msql.append(" FROM  ");
		msql.append("   temp_output_month  ");
		try {
			proc = super.connection.prepareCall("{call PRO_OUTPUT_MONTH()}");
			proc.execute();
			super.connection.commit();
			log.info("综合统计 sheet2 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("销售员", CommonTools.null2String(rest.getString("saleman")));
				map.put("地区", CommonTools.null2String(rest.getString("area_group")));
				map.put("month1", CommonTools.null2String(rest.getString("month1")));
				map.put("month2", CommonTools.null2String(rest.getString("month2")));
				map.put("month3", CommonTools.null2String(rest.getString("month3")));
				map.put("month4", CommonTools.null2String(rest.getString("month4")));
				map.put("month5", CommonTools.null2String(rest.getString("month5")));
				map.put("month6", CommonTools.null2String(rest.getString("month6")));
				map.put("month7", CommonTools.null2String(rest.getString("month7")));
				map.put("month8", CommonTools.null2String(rest.getString("month8")));
				map.put("month9", CommonTools.null2String(rest.getString("month9")));
				map.put("month10", CommonTools.null2String(rest.getString("month10")));
				map.put("month11", CommonTools.null2String(rest.getString("month11")));
				map.put("month12", CommonTools.null2String(rest.getString("month12")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
}
